from orm.model import InvoiceCompany,Document,Customer,AgentKeeping,InvoiceRecord,db,User
from view.baseview import BaseView
from peewee import JOIN
import time
from config import *
from pathlib import Path
import os,csv

class InvoiceCompanyView(BaseView):

    total = 0

    def getUsers(self):
        '''
           在职员工的查询方法
           返回：在职员工列表
            JSON：result
        '''
        rows = User.select(User.id, User.username).where(User.status == "在职");
        users = []
        for user in rows:
            users.append({"id": user.id, "username": user.username})

        return super().resultSet(200, "在职员工查询成功", users)

    def getData(self,queryForm):
        '''
           查询方法
           参数：queryForm： 前端传的查询数据
           返回：公司发票详细情况
            JSON：result
        '''

        where_condition = True
        # where_condition = AgentKeeping.enddate >= time.strftime("%Y-%m-%d 00:00:00", time.localtime())

        if queryForm["keyword"]:
            likestr = "%" + queryForm["keyword"] + "%"
            where_condition = where_condition & ((Document.document_number ** likestr) |
                                                 (Customer.compname ** likestr) | (InvoiceCompany.remarks ** likestr))
        if queryForm['remake']:
            where_condition = where_condition & (AgentKeeping.remake == queryForm['remake'])
        if queryForm['invoice_seal']:
            where_condition = where_condition & (InvoiceCompany.invoice_seal == queryForm['invoice_seal'])
        if queryForm['policeNumber']:
            where_condition = where_condition & ((InvoiceCompany.spaecialticket_number <= queryForm['policeNumber']) |
                                                 (InvoiceCompany.electronicspaecialticket_number <= queryForm[
                                                     'policeNumber']) |
                                                 (InvoiceCompany.generalticket_number <= queryForm['policeNumber']) |
                                                 (InvoiceCompany.electronicgeneralticket_number <= queryForm[
                                                     'policeNumber']))
        if queryForm['status']:
            where_condition = where_condition & (InvoiceCompany.status == queryForm['status'])

        rows = InvoiceCompany.select().join(Customer, JOIN.LEFT_OUTER, on=(InvoiceCompany.customer_id == Customer.id)) \
            .join(AgentKeeping, JOIN.LEFT_OUTER, on=((AgentKeeping.customer_id == InvoiceCompany.customer_id) &
                                                     (AgentKeeping.enddate >= time.strftime("%Y-%m-%d 00:00:00", time.localtime())) )) \
            .join(Document, JOIN.LEFT_OUTER, on=(Document.customer_id == InvoiceCompany.customer_id)).where(
            where_condition)
        print(rows)

        self.total = len(rows)
        rows = rows.paginate(queryForm['pageNo'], queryForm['pagesize'])
        data = []
        for row in rows:

            row_Document = (Document.select().where(Document.customer_id == row.customer_id)).first()
            document_number = ""
            if row_Document != None:
                document_number = row_Document.document_number

            row_Agentkeeping = (AgentKeeping.select(AgentKeeping.remake).where(
                AgentKeeping.customer_id == row.customer_id)).order_by(AgentKeeping.enddate.desc()).first()
            remake = ""
            if row_Agentkeeping != None:
                remake = row_Agentkeeping.remake

            compname = ''
            taxtype = ''
            if row.customer_id != 0:
                compname = row.customer.compname
                taxtype = row.customer.taxtype

            data.append({
                'id': row.id,
                'customer_id': row.customer_id,
                'document_number': document_number,
                'compname': compname,
                'taxtype': taxtype,
                'invoice_seal': row.invoice_seal,
                'remake': remake,
                'remarks': row.remarks,
                'duty_paragraph': row.duty_paragraph,
                'spaecialticket_number': str(row.spaecialticket_number),
                'spaecialticket_quota': str(row.spaecialticket_quota / 10000)[0:-2],
                'electronicspaecialticket_number': str(row.electronicspaecialticket_number),
                'electronicspaecialticket_quota': str(row.electronicspaecialticket_quota / 10000)[0:-2],
                'generalticket_number': str(row.generalticket_number),
                'generalticket_quota': str(row.generalticket_quota / 10000)[0:-2],
                'electronicgeneralticket_number': str(row.electronicgeneralticket_number),
                'electronicgeneralticket_quota': str(row.electronicgeneralticket_quota / 10000)[0:-2],
                'status': row.status
            })

        return data

    def query(self, queryForm):
        '''
           发票管理的主查询方法
           参数：queryForm： 前端传的查询数据
           返回：公司发票详细情况
            JSON：result
        '''

        data = self.getData(queryForm);

        result = {"data": data, 'total': self.total}
        return super().resultSet(200, "公司发票查询成功", result)

    @db.atomic()
    def editClickUpdate(self,updatecompany):
        '''
           编辑方法
           参数：args： 前端传的查询数据和更改的数据
           返回：data : 公司发票详细情况 total:查询的总条数 companys：可以新增的公司列表
            JSON：result
        '''

        # 修改invoice_company字段信息
        InvoiceCompany.update(customer_id=updatecompany['customer_id'],
            remarks=updatecompany['remarks'],
            invoice_seal=updatecompany['invoice_seal'],
            spaecialticket_quota=float(updatecompany['spaecialticket_quota']) * 10000,
            electronicspaecialticket_quota=float(updatecompany['electronicspaecialticket_quota']) * 10000,
            generalticket_quota=float(updatecompany['generalticket_quota']) * 10000,
            electronicgeneralticket_quota=float(updatecompany['electronicgeneralticket_quota']) * 10000,
            status=updatecompany['status'],
            duty_paragraph=updatecompany['duty_paragraph']
            ).where(InvoiceCompany.id == updatecompany['id']).execute()


        # 修改trans_agentkeeping表remake字段信息
        AgentKeeping.update(remake=updatecompany['remake']).where(
            AgentKeeping.customer_id == updatecompany['customer_id']).execute()

        result = ''
        return super().resultSet(200, "公司发票信息修改成功", result)

    @db.atomic()
    def deleteClick(self,args):
        '''
           减票方法
           参数：args： 前端传的查询数据和新增的数据
           返回：data : 公司发票详细情况 total:查询的总条数
            JSON：result
        '''

        insertclick = args['deleteclick']
        queryForm = args['queryForm']

        InvoiceRecord.insert_many({'invoicecompany_id': insertclick['invoicecompany_id'], 'billing_type': insertclick['billingType'],
                                   'billing_time': insertclick['billingTime'] + " 00:00:00", 'billing_number': int(insertclick['number']),
                                    'billing_person': insertclick['billingPerson'],'invoice_status': '正常','operation_type':'减票'}).execute()

        if insertclick['billingType'] == '专票':
            InvoiceCompany.update(
                spaecialticket_number=int(insertclick[insertclick['billingType'] + 'Number']) - int(insertclick['number'])
                ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()
        elif insertclick['billingType'] == '电子专票':
            InvoiceCompany.update(
                electronicspaecialticket_number=int(insertclick[insertclick['billingType'] + 'Number']) - int(insertclick['number'])
                ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()
        elif insertclick['billingType'] == '普票':
            InvoiceCompany.update(
                generalticket_number=int(insertclick[insertclick['billingType'] + 'Number']) - int(insertclick['number'])
                ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()
        elif insertclick['billingType'] == '电子普票':
            InvoiceCompany.update(
                electronicgeneralticket_number=int(insertclick[insertclick['billingType'] + 'Number']) - int(insertclick['number'])
                ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()

        data = self.getData(queryForm);
        tablecolums = self.invoiceRecords(queryForm['billing_time'], insertclick['invoicecompany_id'])

        result = {"data": data, 'total': self.total, "tablecolums": tablecolums}

        return super().resultSet(200, "减票成功", result)

    @db.atomic()
    def insertClick(self, args):
        '''
           增票方法
           参数：args： 前端传的查询数据和新增的数据
           返回：data : 公司发票详细情况 total:查询的总条数
            JSON：result
        '''

        insertclick = args['insertclick']
        queryForm = args['queryForm']

        InvoiceRecord.insert_many(
            {'invoicecompany_id': insertclick['invoicecompany_id'], 'billing_type': insertclick['billingType'],
             'billing_time': insertclick['billingTime'] + " 00:00:00", 'billing_number': int(insertclick['number']),
             'billing_person': insertclick['billingPerson'], 'invoice_status': '正常', 'operation_type': '增票'}).execute()

        if insertclick['billingType'] == '专票':
            InvoiceCompany.update(spaecialticket_number=
                (InvoiceCompany.select(InvoiceCompany.spaecialticket_number).where(InvoiceCompany.id == insertclick['invoicecompany_id']))[0].spaecialticket_number
                + int(insertclick['number'])
            ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()
        elif insertclick['billingType'] == '电子专票':
            InvoiceCompany.update(electronicspaecialticket_number=
                (InvoiceCompany.select(InvoiceCompany.electronicspaecialticket_number).where(InvoiceCompany.id == insertclick['invoicecompany_id']))[0].electronicspaecialticket_number
                + int(insertclick['number'])
            ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()
        elif insertclick['billingType'] == '普票':
            InvoiceCompany.update(generalticket_number=
                (InvoiceCompany.select(InvoiceCompany.generalticket_number).where(InvoiceCompany.id == insertclick['invoicecompany_id']))[0].generalticket_number
                + int(insertclick['number'])
            ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()
        elif insertclick['billingType'] == '电子普票':
            InvoiceCompany.update(electronicgeneralticket_number=
                (InvoiceCompany.select(InvoiceCompany.electronicgeneralticket_number).where(InvoiceCompany.id == insertclick['invoicecompany_id']))[0].electronicgeneralticket_number
                + int(insertclick['number'])
            ).where(InvoiceCompany.id == insertclick['invoicecompany_id']).execute()

        data = self.getData(queryForm);
        tablecolums = self.invoiceRecords(queryForm['billing_time'], insertclick['invoicecompany_id'])

        result = {"data": data, 'total': self.total, "tablecolums": tablecolums}
        return super().resultSet(200, "公司增票成功", result)

    def invoiceRecords(self,billing_times,id):
        '''
           查询某个公司开票记录的方法
           参数：id： 公司id
           返回：公司开票记录
            JSON：result
        '''

        if len(billing_times) != 0:
            rows = InvoiceRecord.select().where(InvoiceRecord.invoicecompany_id == id,InvoiceRecord.billing_time >= billing_times[0] + " 00:00:00" , InvoiceRecord.billing_time <= billing_times[1] + " 00:00:00").order_by(InvoiceRecord.billing_time.desc())
        else:
            rows = InvoiceRecord.select().where(InvoiceRecord.invoicecompany_id == id).order_by(InvoiceRecord.billing_time.desc())

        data = []
        for row in rows:
            data.append({
                'id': row.id,
                'invoicecompany_id': row.invoicecompany_id,
                'billing_type': row.billing_type,
                'billing_time': str(row.billing_time).split(' ')[0],
                'invoice_status': row.invoice_status,
                'billing_number': row.billing_number,
                'operation_type': row.operation_type,
                'billing_person': row.billing_person
            })
        return data

    def columnsQuery(self,args):
        '''
           查询该公司开票记录的方法
           参数：company： 公司信息
           返回：公司开票记录
            JSON：result
        '''
        company_id = args['company_id']
        queryForm = args['queryForm']

        result = self.invoiceRecords(queryForm['billing_time'],company_id)

        return super().resultSet(200, "公司发票记录查询成功", result)

    def updatestate(self,args):
        '''
           作废方法
           参数：record： 减票记录信息
           返回：data 公司减票记录
            JSON：result
        '''

        queryForm = args['queryForm']
        record = args['record']

        InvoiceRecord.update(
            invoice_status='作废'
        ).where(InvoiceRecord.id == record['id']).execute()

        invoiceCompany = InvoiceCompany.select().where(
            InvoiceCompany.id == record['invoicecompany_id']).first()

        if record['billing_type'] == '专票':
            if record['operation_type'] == '减票':
                InvoiceCompany.update(spaecialticket_number= invoiceCompany.spaecialticket_number + int(record['billing_number'])).where(InvoiceCompany.id == record['invoicecompany_id']).execute()
            elif record['operation_type'] == '增票':
                InvoiceCompany.update(spaecialticket_number= invoiceCompany.spaecialticket_number - int(record['billing_number'])).where(InvoiceCompany.id == record['invoicecompany_id']).execute()
        elif record['billing_type'] == '电子专票':
            if record['operation_type'] == '减票':
                InvoiceCompany.update(electronicspaecialticket_number= invoiceCompany.electronicspaecialticket_number + int(record['billing_number'])).where(
                    InvoiceCompany.id == record['invoicecompany_id']).execute()
            elif record['operation_type'] == '增票':
                InvoiceCompany.update(electronicspaecialticket_number= invoiceCompany.electronicspaecialticket_number - int(record['billing_number'])).where(
                    InvoiceCompany.id == record['invoicecompany_id']).execute()
        elif record['billing_type'] == '普票':
            if record['operation_type'] == '减票':
                InvoiceCompany.update(generalticket_number= invoiceCompany.generalticket_number + int(record['billing_number'])
                                      ).where(InvoiceCompany.id == record['invoicecompany_id']).execute()
            elif record['operation_type'] == '增票':
                InvoiceCompany.update(generalticket_number= invoiceCompany.generalticket_number - int(record['billing_number'])
                                      ).where(InvoiceCompany.id == record['invoicecompany_id']).execute()
        elif record['billing_type'] == '电子普票':
            if record['operation_type'] == '减票':
                InvoiceCompany.update(electronicgeneralticket_number= invoiceCompany.electronicgeneralticket_number + int(record['billing_number'])
                                      ).where(InvoiceCompany.id == record['invoicecompany_id']).execute()
            elif record['operation_type'] == '增票':
                InvoiceCompany.update(electronicgeneralticket_number= invoiceCompany.electronicgeneralticket_number - int(record['billing_number'])
                                      ).where(InvoiceCompany.id == record['invoicecompany_id']).execute()

        tablecolums = self.invoiceRecords(queryForm['billing_time'], record['invoicecompany_id'])

        data = self.getData(queryForm);

        result = {"data": data, 'total': self.total, "tablecolums": tablecolums}

        return super().resultSet(200, "发票作废成功", result)

    def insertCompany(self,args):
        '''
            发票新增
            参数：args： 前端传的查询数据与新增数据
           返回："data": 公司发票详细情况, 'total': 总条数, "companys": 可以新增的公司列表
            JSON：result
        '''

        queryForm = args['queryForm']
        insertcompany = args['insertcompany']

        InvoiceCompany.insert_many(
            {'customer_id': insertcompany['customer_id'], 'invoice_seal': insertcompany['invoice_seal'],
             'remarks': insertcompany['remarks'],'duty_paragraph': insertcompany['duty_paragraph'] ,
             'spaecialticket_number': int(insertcompany['spaecialticket_number']),
             'spaecialticket_quota': float(insertcompany['spaecialticket_quota']) * 10000,
             'electronicspaecialticket_number': int(insertcompany['electronicspaecialticket_number']),
             'electronicspaecialticket_quota': float(insertcompany['electronicspaecialticket_quota']) * 10000,
             'generalticket_number': int(insertcompany['generalticket_number']),
             'generalticket_quota': float(insertcompany['generalticket_quota']) * 10000,
             'electronicgeneralticket_number': int(insertcompany['spaecialticket_number']),
             'electronicgeneralticket_quota': float(insertcompany['electronicgeneralticket_quota']) * 10000,
             'status' : '正常'}).execute()

        # 托管的修改
        AgentKeeping.update(
            remake=insertcompany['remake']
        ).where(AgentKeeping.customer_id == insertcompany['customer_id']).execute()

        data = self.getData(queryForm);
        companys = self.getCompanys()['data'];

        result = {"data": data, 'total': self.total, "companys": companys}
        return super().resultSet(200, "公司新增成功", result)

    def getCompanys(self):
        '''
           查询公司名称方法
           返回："data":可以新增的公司列表
            JSON：result
        '''

        data = []
        rows = AgentKeeping.select(AgentKeeping.customer_id,AgentKeeping.remake).where(AgentKeeping.enddate >= time.strftime("%Y-%m-%d 00:00:00", time.localtime()))
        for row in rows:
            if len(InvoiceCompany.select().where(InvoiceCompany.customer_id == row.customer_id)) != 0:
                continue
            customer_row = Customer.select(Customer.compname).where(Customer.id == row.customer_id)
            data.append({
                'value':customer_row[0].compname,
                'customer_id':row.customer_id,
                'remake':row.remake
            })

        result = data
        return super().resultSet(200, "公司名称列表查询成功", result)

    @db.atomic()
    def importfile(self, request):
        """
            导入原件统计表
        """
        try:
            if request.method == 'POST' and 'file' in request.files:
                file = request.files['file']
                path = UPLOAD_PATH
                filename = path + "/" + file.filename
                my_path = Path(path)
                if not my_path.exists():
                    # 指定的文件或目录不存在:
                    os.makedirs(path)
                file.save(filename)
                rownumber = 0
                with open(filename) as csvfile:
                    reader = csv.reader(csvfile)
                    header = next(reader)
                    if len(header) != 16:
                        return super().resultSet(400, "文件格式不正确！", [])
                    print(header)
                    print(header)
                    for param in reader:
                        rownumber = rownumber + 1
                        print(rownumber)
                        compnamefind = Customer.select().where(Customer.compname == param[1]).first()
                        if compnamefind:
                            # 如果公司名称存在
                            InvoiceCompany.create(
                                customer_id=compnamefind.id,
                                # 税号
                                duty_paragraph=param[4],
                                # 发票号
                                invoice_seal=param[5],
                                # 专票数量
                                spaecialticket_number=param[6],
                                # 专票限额
                                spaecialticket_quota=param[7],
                                # 电子专票数量
                                electronicspaecialticket_number=param[8],
                                # 电子专票限额
                                electronicspaecialticket_quota=param[9],
                                # 普票数量
                                generalticket_number=param[10],
                                # 普票限额
                                generalticket_quota=param[11],
                                # 电子普票数量
                                electronicgeneralticket_number=param[12],
                                # 电子普票限额
                                electronicgeneralticket_quota=param[13],
                                # 备注
                                remarks=param[14],
                                # 状态
                                status="正常"
                            )
                        else:
                            # 如果公司名称不存在
                            InvoiceCompany.create(
                                customer_id=0,
                                # 税号
                                duty_paragraph=param[4],
                                # 发票号
                                invoice_seal=param[5],
                                # 专票数量
                                spaecialticket_number=param[6],
                                # 专票限额
                                spaecialticket_quota=param[7],
                                # 电子专票数量
                                electronicspaecialticket_number=param[8],
                                # 电子专票限额
                                electronicspaecialticket_quota=param[9],
                                # 普票数量
                                generalticket_number=param[10],
                                # 普票限额
                                generalticket_quota=param[11],
                                # 电子普票数量
                                electronicgeneralticket_number=param[12],
                                # 电子普票限额
                                electronicgeneralticket_quota=param[13],
                                # 备注
                                remarks=param[14] + '[' + param[1] + ']',
                                # 状态
                                status="公司名不存在"
                            )
            return super().resultSet(200, "共" + str(rownumber) + "行数据导入成功！", [])
        except IOError:
            return super().resultSet(400, "数据不正确，导入失败！", [])
        raise Exception("导入失败")

